﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessMenuConfiguration
// 文件功能描述：菜单配置数据访问层
//
// 
// 创建标识：2012-05-14 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DAO.WaiGaoQiao
{
    public class DataAccessMenuConfiguration
    {
        /// <summary>
        /// 获取菜单配置数量
        /// </summary>
        /// <returns></returns>
        public int GetMenuConcount()
        {
            sqldb db = new sqldb();
            string query = "select count(1) from Con_MenuConfiguration_View";
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取菜单配置数量
        /// </summary>
        /// <returns></returns>
        public int GetMenuConcount(string where)
        {
            sqldb db = new sqldb();
            string query = "select count(1) from Con_MenuConfiguration_View " + where;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }

        /// <summary>
        /// 获取菜单配置信息
        /// </summary>
        /// <returns></returns>
        public DataSet SelMenuConInfo(int pagesize, int startindex, string strquery)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                int startrow = pagesize * (startindex - 1);
                int endrow = pagesize * startindex + 1;
                string query = "select * from (select ROW_NUMBER() OVER(ORDER BY MenuConDate desc,NodeID,PeriodCode,MenuType) rownumber,* " +
                               "from Con_MenuConfiguration_View " + strquery + ") B "
                             + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        public DataSet SelLeftBox(string strquery)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from Con_Menus where 1=1 " + strquery;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 保存(添加菜单配置)
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int SaveMenuConAdd(object[] obj, string[] smenall)
        {
            //sqldb db = new sqldb();
            //int count = 0;

            ////string query = "select COUNT(*) from Con_MenuConfiguration where MenuConDate='" + obj[0] + "' and NodeID=" + obj[2] + " and PeriodCode=" + obj[3];
            ////object objcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            ////if (Convert.ToInt32(objcount) + smenall.Length > 5)
            ////{
            ////    count = -1;
            ////}
            ////else
            ////{
            //string query = "";

            //    for (int i = 0; i < smenall.Length; i++)
            //    {
            //        query = "insert into Con_MenuConfiguration(MenuId,MenuConDate,Operator,NodeID,PeriodCode) values(" + smenall[i] + ",'" + obj[0] + "','" + obj[1] + "','" + obj[2] + "','" + obj[3] + "')";
            //        int rowcount = db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, query);
            //        if (rowcount != 0)
            //        {
            //            count++;
            //        }
            //    }
            ////}
            //return count;
            //建立连接并打开
            int count = 0;
            SqlConnection conn = new SqlConnection(GetConntionString.conntionstring());
            SqlConnection myConn = conn;
            myConn.Open();
            SqlCommand myComm = new SqlCommand();
            SqlTransaction myTran;
            //创建一个事务
            myTran = myConn.BeginTransaction();
            string query = "";
            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分
                //下面绑定连接和事务对象
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库
                for (int i = 0; i < smenall.Length; i++)
                {
                    query = "insert into Con_MenuConfiguration(MenuId,MenuConDate,Operator,NodeID,PeriodCode) values(" + smenall[i] + ",'" + obj[0] + "','" + obj[1] + "','" + obj[2] + "','" + obj[3] + "')";
                    myComm.CommandText = query;
                    int rowcount = myComm.ExecuteNonQuery();//操作1
                    if (rowcount != 0)
                    {
                        count++;
                    }
                }
                //提交事务
                myTran.Commit();
                return count;
            }
            catch (Exception err)
            {
                throw new ApplicationException("事务操作出错，系统信息：" + err.Message);
            }
            finally
            {
                myConn.Close();
            }

        }

        //public void DoTran()
        //{ 　//建立连接并打开
        //    SqlConnection conn = new SqlConnection(GetConntionString.conntionstring());
        //    SqlConnection myConn = conn;
        //    myConn.Open();
        //    SqlCommand myComm = new SqlCommand();
        //    //SqlTransaction myTran=new SqlTransaction();
        //    //注意，SqlTransaction类无公开的构造函数
        //    SqlTransaction myTran;
        //    //创建一个事务
        //    myTran = myConn.BeginTransaction();
        //    try
        //    {
        //        //从此开始，基于该连接的数据操作都被认为是事务的一部分
        //        //下面绑定连接和事务对象
        //        myComm.Connection = myConn;
        //        myComm.Transaction = myTran; //定位到pubs数据库
        //        myComm.CommandText = "USE pubs";
        //        myComm.ExecuteNonQuery();//操作1
        //        myComm.CommandText = ""; //操作2
        //        myComm.ExecuteNonQuery();
        //        //提交事务
        //        myTran.Commit();
        //    }
        //    catch (Exception err)
        //    {
        //        throw new ApplicationException("事务操作出错，系统信息：" + err.Message);
        //    }
        //    finally
        //    {
        //        myConn.Close();
        //    }
        //}


        /// <summary>
        /// 保存(修改菜单配置)
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int SaveMenuConEdit(object[] obj, string[] smenall)
        {
            SqlConnection conn = new SqlConnection(GetConntionString.conntionstring());
            SqlConnection myConn = conn;
            myConn.Open();
            SqlCommand myComm = new SqlCommand();
            SqlTransaction myTran;
            //创建一个事务
            myTran = myConn.BeginTransaction();
            string query = "";
            int count = 0;
            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分
                //下面绑定连接和事务对象
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库
                query = "delete from Con_MenuConfiguration where MenuConDate='" + obj[0] + "' and NodeID=" + obj[2] + " and PeriodCode=" + obj[3];
                myComm.CommandText = query;
                myComm.ExecuteNonQuery();//操作1
                //myComm.CommandText = query;
                for (int i = 0; i < smenall.Length; i++)
                {
                    query = "insert into Con_MenuConfiguration(MenuId,MenuConDate,Operator,NodeID,PeriodCode) values(" + smenall[i] + ",'" + obj[0] + "','" + obj[1] + "','" + obj[2] + "','" + obj[3] + "')";
                    myComm.CommandText = query;
                    int rowcount = myComm.ExecuteNonQuery();//操作1
                    if (rowcount != 0)
                    {
                        count++;
                    }
                }
                //提交事务
                myTran.Commit();
                return count;
            }
            catch (Exception err)
            {
                throw new ApplicationException("事务操作出错，系统信息：" + err.Message);
            }
            finally
            {
                myConn.Close();
            }
        }
        /// <summary>
        /// 菜单配置批量导入
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public string ExcelDataSource(string filepath, string sheetname, string Operator)
        {
            /*
             *将excel中的数据导入到DataSet中 
             */
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn1 = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            /*
             *将dataset中的时间循环取出放在 strAllDate字符串中并用逗号分隔
             */
            string strAllDate = string.Empty;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (ds.Tables[0].Rows[i + 2][0].ToString() == "")
                {
                    break;
                }
                strAllDate += "'" + ds.Tables[0].Rows[i + 2][0].ToString() + "'";
                strAllDate += ",";
            }
            strAllDate = strAllDate.Remove(strAllDate.Length - 1);
            ////////////////////////////////////
            /*
             *创建事务
             */
            SqlConnection conn = new SqlConnection(GetConntionString.conntionstring());
            SqlConnection myConn = conn;
            myConn.Open();
            SqlCommand myComm = new SqlCommand();
            SqlTransaction myTran;
            //创建一个事务
            myTran = myConn.BeginTransaction();
            string query = "";
            int count = 0;
            int rowcount = 0;
            int imenuid = 0;
            string newcount = string.Empty;
            /*
             *  查询出食堂，放入ds1中 
             */
            sqldb db = new sqldb();
            SqlConnection conns = new SqlConnection(GetConntionString.conntionstring());
            query = "select * from Yax_Node where ConFlag=1";
            DataSet ds1 = db.ExecuteDataset(conns, CommandType.Text, query);

            /*
             *  先查询出模板中的第一种餐次的编号放入iPeriodCode中
             */
            int iPeriodCode = 0;
            object obj1 = db.ExecuteScalar(conns, CommandType.Text, "select PeriodCode from Con_Period where PeriodName='" + ds.Tables[0].Rows[2][2].ToString() + "'");
            iPeriodCode = Convert.ToInt32(obj1);

            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分
                //下面绑定连接和事务对象
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库
                query = "delete from Con_MenuConfiguration where MenuConDate in(" + strAllDate + ")";
                myComm.CommandText = query;
                rowcount = myComm.ExecuteNonQuery();
                if (rowcount != 0)
                {
                    count++;
                }
                for (int i = 2; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i][0].ToString().Trim() == "")
                    {
                        i++;
                        query = "select PeriodCode from Con_Period where PeriodName='" + ds.Tables[0].Rows[i][2].ToString() + "'";
                        myComm.CommandText = query;
                        iPeriodCode = Convert.ToInt32(myComm.ExecuteScalar());
                    }
                    for (int j = 3; j <= 23; j++)
                    {
                        if (ds.Tables[0].Rows[i][j].ToString().Trim() != "")
                        {
                            query = "select MenuId from con_menus where MenuName='" + ds.Tables[0].Rows[i][j].ToString() + "'"; //啊实打实的
                            myComm.CommandText = query;
                            imenuid = Convert.ToInt32(myComm.ExecuteScalar());
                            if (imenuid == 0)
                            {
                                newcount = ds.Tables[0].Rows[i][j].ToString();
                                //throw new Exception(newcount);
                                return newcount;
                            }
                            for (int z = 0; z < ds1.Tables[0].Rows.Count; z++)
                            {
                                query = "insert into Con_MenuConfiguration values('" + imenuid + "','" + ds.Tables[0].Rows[i][0].ToString() + "','" + Operator + "','" + ds1.Tables[0].Rows[z]["NodeID"].ToString() + "','" + iPeriodCode + "')";
                                myComm.CommandText = query;
                                rowcount = myComm.ExecuteNonQuery();
                                if (rowcount != 0)
                                {
                                    count++;
                                }
                            }
                        }
                        else
                        {
                            int jsdf = 0;
                        }
                    }
                }
                myTran.Commit();
            }
            catch (Exception err)
            {
                throw new ApplicationException("事务操作出错，系统信息：" + err.Message);
            }
            finally
            {
                myConn.Close();
            }
            ////////////////////////////////////
            newcount = count.ToString();
            return "1";
        }

        /// <summary>
        /// 删除菜单配置
        /// </summary>
        /// <param name="roleid"></param>
        /// <returns></returns>
        public int DeleteMenuCon(string id)
        {
            sqldb db = new sqldb();
            string query = "Delete from Con_MenuConfiguration where MenuConId = '" + id + "'";
            int rowcount = db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.Text, query);
            return rowcount;
        }





        public DataSet SelRightBoxEdit(string Date, string ID)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select NodeID,PeriodCode from Con_MenuConfiguration_View where MenuConId=" + ID;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                query = "select * from Con_MenuConfiguration_View where MenuConDate='" + Date + "' and NodeID=" + ds.Tables[0].Rows[0][0].ToString() + " and PeriodCode=" + ds.Tables[0].Rows[0][1].ToString();
                ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        public DataSet SelEdit(string Date, string ID)
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select NodeID,PeriodCode,MenuConDate,Nodename,PeriodName from Con_MenuConfiguration_View where MenuConId=" + ID;
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        public DataSet SelNodeID()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from Yax_Node where ConFlag=1";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        public DataSet SelPeriodCode()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select * from Con_Period where UseFlag=1";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
    }
}
